const { Pool } = require('pg');

// 数据库连接配置
const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'dw_sql_manager',
  user: 'postgres',
  password: '1234Qwer'
});

async function addPathField() {
  const client = await pool.connect();
  
  try {
    console.log('开始为folders表添加path字段...');
    
    // 开始事务
    await client.query('BEGIN');
    
    // 添加path字段
    await client.query('ALTER TABLE folders ADD COLUMN IF NOT EXISTS path VARCHAR(1000)');
    console.log('✓ 添加path字段成功');
    
    // 创建函数来计算路径
    await client.query(`
      CREATE OR REPLACE FUNCTION calculate_folder_path(folder_id UUID)
      RETURNS VARCHAR(1000) AS $$
      DECLARE
          result_path VARCHAR(1000) := '';
          current_id UUID := folder_id;
          current_name VARCHAR(255);
          parent_id UUID;
      BEGIN
          WHILE current_id IS NOT NULL LOOP
              SELECT f.name, f.parent_id INTO current_name, parent_id FROM folders f WHERE f.id = current_id;
              
              IF result_path = '' THEN
                  result_path := current_name;
              ELSE
                  result_path := current_name || '/' || result_path;
              END IF;
              
              current_id := parent_id;
          END LOOP;
          
          RETURN result_path;
      END;
      $$ LANGUAGE plpgsql;
    `);
    console.log('✓ 创建路径计算函数成功');
    
    // 更新所有现有文件夹的路径
    await client.query(`
      UPDATE folders 
      SET path = calculate_folder_path(id)
      WHERE path IS NULL OR path = ''
    `);
    console.log('✓ 更新现有文件夹路径成功');
    
    // 创建触发器函数
    await client.query(`
      CREATE OR REPLACE FUNCTION update_folder_path()
      RETURNS TRIGGER AS $$
      DECLARE
          parent_path VARCHAR(1000);
      BEGIN
          -- 如果有父文件夹，构建完整路径
          IF NEW.parent_id IS NOT NULL THEN
              SELECT path INTO parent_path FROM folders WHERE id = NEW.parent_id;
              NEW.path = parent_path || '/' || NEW.name;
          ELSE
              -- 根文件夹
              NEW.path = NEW.name;
          END IF;
          
          RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `);
    console.log('✓ 创建触发器函数成功');
    
    // 创建触发器
    await client.query(`
      DROP TRIGGER IF EXISTS update_folder_path_trigger ON folders;
      CREATE TRIGGER update_folder_path_trigger
          BEFORE INSERT OR UPDATE ON folders
          FOR EACH ROW EXECUTE FUNCTION update_folder_path();
    `);
    console.log('✓ 创建触发器成功');
    
    // 提交事务
    await client.query('COMMIT');
    console.log('\n🎉 folders表path字段添加完成！');
    
    // 验证结果
    const result = await client.query('SELECT id, name, path FROM folders ORDER BY path');
    console.log('\n当前文件夹结构:');
    result.rows.forEach(row => {
      console.log(`- ${row.name}: ${row.path}`);
    });
    
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('添加path字段失败:', error.message);
  } finally {
    client.release();
    await pool.end();
  }
}

addPathField();